Excel Tip of the Week #433 - Revisiting Excel file types

This was first discussed in TOTW #258.

Above is the full list of the file types that my Excel 365 offers from the “Save As” menu. This is quite a bewildering selection at first, but many of these are very obscure and you’re unlikely to ever need to use them. We’re going to focus on the most common and most useful, explain the differences between them, and when you’d want to use each.

Excel workbook – .xlsx

This is the default file format for all Excel versions from 2007 onwards. It’s actually a kind of compressed file – a series of connected XML files representing the different parts of the file, that are assembled into the complete file when you open it. It is possible to see the guts of an XLSX file if you change the file extension and run it through an unzipper!

An XLSX file is secure and stable, and is unlikely to be fully corrupted and unreadable. The format is also accessible by other spreadsheet software.

Excel macro-enabled workbook - .xlsm

XLSM is identical to XLSX, except for that it also allows the file to store VBA code – whether in the form of macros or user-defined functions. This uses a separate file type to make quarantining potentially hazardous VBA code simpler – no XLSX file can contain macros, so many organisations block XLSM files or only allow them to be opened with limited permissions. If your file does not have macros in them, you should always save as XLSX to avoid any confusion.

Excel binary workbook - .xlsb

This is a simpler way of saving Excel files that does not use compressed XML files, but just saves the workbook in a single flat file. Typically an XLSB will be smaller than the XLSX version of the same file. However, XLSB files are more easily corrupted as they have only a single file to work with, and cannot be opened by third-party software. XLSX is usually a better bet.

Excel 97-2003 workbook - .xls

This is the original Excel file format. It is considerably more limited than a XLSX – for example allowing only 65,536 rows and 256 columns, instead of the 1,048,576 rows and 16,384 columns that XLSX allows. Data beyond these limits will simply be lost. This file type is deprecated and may lead to loss of data or formatting, sometimes without warning. Do not use it unless you are for some reason working with pre-Excel 2007 users.

Excel template - .xltx

An Excel template file is used by Excel to create new workbooks or worksheets. If you want to change the default new workbook or new worksheet that Excel creates when you open a fresh file or tab, then you can do that by saving your design as an XLTX file in the Excel startup directory with the name Book or Sheet respectively. The default location for that file will be opened as soon as you select XLTX from the Save As menu; it’s typically somewhere along the lines of C:\Program Files\Microsoft Office\root\Office16\XLSTART.

CSV (Comma delimited) - .csv

One of the simplest possible file formats, CSV consists only of values separated by commas, with extra commas to mark the ends of rows. Many programs use CSV as a simple export/import format, so it’s not unusual to get data in this format, or to need to save in this format to enable an upload. But the format is extremely limited, allowing only data with no formatting, no formulas, and only one sheet. If you are working with a CSV source and plan on adding anything to it, make sure you remember to save it in a different file format as soon as possible to minimise the risk of losing your work. And likewise if you need to save a CSV version of a more complex file, save it in a more fully-featured format first.

Note that there are also Macintosh versions of the CSV format available.

Excel Add-in - .xlam

An Excel Add-in is an Excel file containing VBA code that you can add to your Excel installation to simplify gaining access to routines or user-defined functions that you need to use frequently. It’s essentially the same as an XLSM file except for that it allows you to add it to Excel using the Excel Options => Add-ins menu.

Check out TOTW #410 for more on how to use custom Excel add-ins.

Having an idea what kinds of files are out there for Excel to work with and save in can be really handy – so keep these in mind next time you’re thinking about saving your file.

• Previous post - Date creating formulas redux
• TOTW index
• Next time – Revisiting making sets of PivotTables!